'''
sql client based on lib sqlalchemy
'''
import threading
import traceback

# python -m pip install SQLAlchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

from sql_server.tables import Kitting_Product_Table, Kitting_ProductKeyMaterial_Table

from log import logger
class sql_server_client:
    static_lock = threading.Lock()
    def __init__(self,server = r'192.168.12.89', user = "ass",password = "123", database = 'KittingDB'):
        self.server = server  # 连接服务器地址 ch71w0120  199.63.152.66
        self.username = user  # 连接帐号  MdcsSzPr-rw
        self.password = password # 连接密码 X7tu5#99RT1
        self.database = database # LineKitting

        self.engine = create_engine(
            f'mssql+pyodbc://{self.username}:{self.password}@{self.server}/{database}?driver=ODBC+Driver+17+for+SQL+Server')

        self.Session = sessionmaker(bind=self.engine)
    def connect(self):

        self.session = self.Session()
        print(f'sqlserver connection:  {self.server} {self.username} {self.password} {self.database}')

    def select(self, sql_query_str = 'SELECT * FROM dbo.Kitting_newunittype'):
        sql_server_client.static_lock.acquire()
        # self.connect()
        session = self.Session()
        print(f'sql_query_str: {sql_query_str}')
        # 执行查询
        # result = self.session.execute(text(sql_query_str))
        result = session.execute(text(sql_query_str))
        # result = session.execute(text("SELECT * FROM Kitting_User"))
        res = []
        for row in result:
            # print(row)
            record_dict = dict(zip(result.keys(), row))
            # print(record_dict)
            res.append(record_dict)

        # self.close()
        session.close()
        sql_server_client.static_lock.release()
        return res # [{}, {}]


    def insert(self, record):
        sql_server_client.static_lock.acquire()
        is_rollback = False
        print(f'insert {record.__dict__}')
        # self.connect()
        # self.session.add(record)
        session = self.Session()
        session.add(record)
        try:
            # self.session.commit()
            session.commit()
        except Exception as e:
            # self.session.rollback()
            session.rollback()
            is_rollback = True
            logger.info(f"插入记录回滚 {e}")
            logger.info(traceback.format_exc())
        finally:
            # self.session.close()
            session.close()
            sql_server_client.static_lock.release()
            return not is_rollback

    def close(self):
        print('sqlserver: close')
        self.session.close()

    @staticmethod
    def create_inserting_record(sn, unit_type, boxnumber, date, OrderNo, EID):

        kp_record = Kitting_Product_Table(
            sn=sn, unit_type=unit_type,
            boxnumber=boxnumber, date=date,
            OrderNo=OrderNo, EID=EID)

        return kp_record

def test_sql_server_client():
    # ssclient = sql_server_client(server='ch71w0120', user='MdcsSzPr-rw',
    #                              password='X7tu5#99RT1', database='LineKitting')
    ssclient = sql_server_client(server='199.63.152.27', user='MdcsSzPr-rw',
                                 password='X7tu5#99RT1', database='MDCSPr')
    # ssclient.connect()

    # sql_insert_str = \
    #     ("INSERT INTO dbo.Kitting_User "
    #      "([F_Id], [F_UserName],[F_Password],[SecurityRight]) "
    #      "VALUES (%s, %s, %s, %s)")
    # values_list = (('3','kit_user3', '123', 'sr1'),
    #                ('5','kit_user2', '123', 'sr2'))
    # ssclient.insert(sql_insert_str,values_list) # 插入


    # sql_update_str = "UPDATE [KittingDB].[dbo].[Kitting_User] SET [F_Password]='12345' WHERE [F_UserName]='KIT_USER3'"
    # ssclient.update(sql_update_str)
    #
    # sql_delete_str = "DELETE FROM [KittingDB].[dbo].[Kitting_User] WHERE [F_UserName]='KIT_USER3'"
    # ssclient.delete(sql_delete_str)

    # sql_query_str = 'SELECT * FROM Kitting_User'
    sql_query_str = "SELECT TOP 10 * FROM tblItems"
    # print(sql_query_str)
    row = ssclient.select(sql_query_str) # 查询
    for ro in row:
        print(ro)

    # ssclient.close()

if __name__ =='__main__':
    test_sql_server_client()